package com.bdy.lm.browser.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import cn.itcast.jdbc.JdbcUtils;
import cn.itcast.jdbc.TxQueryRunner;

import com.bdy.lm.browser.domain.CarMeasure;
import com.bdy.lm.browser.domain.LocationTracks;
import com.bdy.lm.browser.domain.StandardRoute;
import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;

public class VerifyAdjustDao {
	
	private QueryRunner qr = new TxQueryRunner();
	private Connection con = null;
	private Statement stmt = null;
	private ResultSet rs = null;
	private PreparedStatement ps = null;
	
	public List<StandardRoute> queryStandardRoute () {
		try {
			String sql = "SELECT * FROM standardroute_tb";
			return qr.query(sql, new BeanListHandler<StandardRoute>(StandardRoute.class));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}	
	
	public boolean addStandardRoute(StandardRoute standardRoute) {
		try {
			String sql = "INSERT INTO standardroute_tb VALUES(?,?,?,?,?,?,?,?,?,?,?)";
			Object[] params = {	standardRoute.getNum(), 
								standardRoute.getAlongitude(), standardRoute.getAlatitude(), standardRoute.getAaltitude(), standardRoute.getAradius(),
								standardRoute.getBlongtitde(), standardRoute.getBlatitude(), standardRoute.getBaltitude(), standardRoute.getBradius(),
								standardRoute.getSmeasure(), standardRoute.getStandard()};
			if (qr.update(sql, params) > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public StandardRoute queryStandardRouteByNum(int num) {
		try {
			String sql = "SELECT * FROM standardroute_tb WHERE num=?";
			return qr.query(sql, new BeanHandler<StandardRoute>(StandardRoute.class), num);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public boolean modifyStandardRoute(StandardRoute sRoute) {
		try {
			String sql = "UPDATE standardroute_tb SET Alongitude=?,Alatitude=?,Aaltitude=?,Aradius=?,Blongtitde=?,Blatitude=?,Baltitude=?,Bradius=?,Smeasure=?,standard=? WHERE num=?";
			Object[] params = {	sRoute.getAlongitude(), sRoute.getAlatitude(), sRoute.getAaltitude(),sRoute.getAradius(), 
								sRoute.getBlongtitde(), sRoute.getBlatitude(), sRoute.getBaltitude(),sRoute.getBradius(),
								sRoute.getSmeasure(), sRoute.getStandard(), sRoute.getNum()	};
			if (qr.update(sql, params) > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public boolean deleteStandardRoute(List<Integer> nums) {

		try {
			String sql = "DELETE FROM standardroute_tb WHERE num=?";
			int sum = 0;
			for (int i = 0; i < nums.size(); i++) {
				Object[] params = { nums.get(i) };
				sum += qr.update(sql, params);
			}
			if (sum == nums.size()) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public List<CarMeasure> queryCarMeasure() {
		try {
			String sql = "SELECT * FROM scar_tb";
			return qr.query(sql, new BeanListHandler<CarMeasure>(CarMeasure.class));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public List<LocationTracks> queryVerifyTrace(String verifyTime) {
		byte[] re = null;
		try {
			con = JdbcUtils.getConnection();
			ps = con.prepareStatement("SELECT locationTracks FROM scar_tb WHERE verifyTime='" + verifyTime + "'");
			rs = ps.executeQuery();
			if(rs.next()) {
				re = new byte[rs.getBinaryStream("locationTracks").available()];
				rs.getBinaryStream("locationTracks").read(re);
			}
			List<LocationTracks> list = new Gson().fromJson(new String(re), new TypeToken<List<LocationTracks>>(){}.getType());
			return list;
		} catch (Exception e1) {
			e1.printStackTrace();
			return null;
		}
	}
}
